package cn.jdy.tools;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;

import cn.jdy.framework.util.JsonUtil;

/**
 * 使用poi读取xls
 * 用于解决 日常通过xls获取数据，进行数据提取
 * 
 * @author zhaigt
 * 
 */
public class RWxls {
  private static final String INTPUT_PATH = "D:/git/qwe-project/xyyzglj/doc/邮政管理局-电子地图数据梳理表格.xls";

  /**
   * @功能 读取xls内容
   * @参数 参数名 参数说明
   * @返回值 返回值名 返回值说明
   */
  @Test
  public void readExcel() throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(INTPUT_PATH)));
    HSSFSheet sheet = null;
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
      sheet = workbook.getSheetAt(i);
      for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {// 获取每行
        HSSFRow row = sheet.getRow(j);
        for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {// 获取每个单元格
          System.out.print(row.getCell(k) + "\t");
        }
        System.out.println();//换行
      }
      System.out.println("---Sheet表" + i + "处理完毕---");
    }
  }
  
  /**
   * @功能 读取xls内容
   * @参数 参数名 参数说明
   * @返回值 返回值名 返回值说明
   */
  @Test
  public void readExcel2() throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(INTPUT_PATH)));
    HSSFSheet sheet = null;
      sheet = workbook.getSheetAt(0);
      for (int j = 2; j < sheet.getPhysicalNumberOfRows(); j++) {// 获取每行
        HSSFRow row = sheet.getRow(j);
        // 按照目录循环，判断目录下的文件名是否存在
        String dirName = row.getCell(0).toString().replace(".0", "");
        String filesName = row.getCell(16).toString();
        File file = new File("D:/git/qwe-project/xyyzglj/src/main/webapp/data/"+dirName);
        File[] files = file.listFiles();
        for (File f : files) {// 判断不村咋的文件名就删除文件
          if(filesName.indexOf(f.getName())==-1){
            f.delete();
            System.out.println(f.getName());
          }
        }
//        System.out.print(row.getCell(0).toString().replace(".0", "") + "\t" + row.getCell(16));
//        for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {// 获取每个单元格
//          System.out.print(row.getCell(k) + "\t");
//        }
        System.out.println();//换行
    }
    
  }
//  public static void main(String[] args) {
//    System.out.println("1.jpg,2.jpg,3.jpg,4.jpg,6.jpg,7.jpg".indexOf("2.jpg"));
//  }
  /**
   * @功能 读取xls内容转为为json格式
   * @参数 参数名 参数说明
   * @返回值 返回值名 返回值说明
   */
  @Test
  public void readExcel2json() throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(INTPUT_PATH)));
    HSSFSheet sheet = null;
    List<YzBo> list = new ArrayList<YzBo>();
    
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
      sheet = workbook.getSheetAt(i);
      for (int j = 2; j < sheet.getPhysicalNumberOfRows(); j++) {// 获取每行
        HSSFRow row = sheet.getRow(j);
//        for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {// 获取每个单元格
//          System.out.print(row.getCell(k) + "\t");
          YzBo bo = new YzBo();
          bo.setId(row.getCell(0).toString().replace(".0", ""));
          bo.setNode(row.getCell(1).toString());
          bo.setArea(row.getCell(2).toString());
          bo.setName(row.getCell(3).toString());
          bo.setAddress(row.getCell(4).toString());
          bo.setScope(row.getCell(5).toString());
          bo.setOntime(row.getCell(6).toString());
          String tel = row.getCell(7).toString().replace(".0", "");
          bo.setTel(tel.length()>7 ? tel : "0710-"+tel);
          bo.setZipcode(row.getCell(8).toString().replace(".0", ""));                 
          bo.setMianji(row.getCell(9).toString());                  
          bo.setRenkou(row.getCell(10).toString());                  
          bo.setPostime(row.getCell(11).toString());                 
          bo.setFuwubanj(row.getCell(12).toString());                
          bo.setFuwurenkou(row.getCell(13).toString());              
          bo.setLng(row.getCell(14).toString());                  
          bo.setLat(row.getCell(15).toString());                  
          bo.setImages(row.getCell(16).toString());
          list.add(bo);
//        }
        System.out.println();//换行
      }
      System.out.println("---Sheet表" + i + "处理完毕---");
      System.out.println(JsonUtil.toJson(list));
    }

  }
  
  /**
   * 读取一个目录下的子目录文件名
   */
  @Test
  public void readfilename() {
    File file = new File("D:/git/qwe-project/xyyzglj/src/main/webapp/data/");
    File[] files = file.listFiles();
    for (File f : files) {// 判断不村咋的文件名就删除文件
      if(f.isDirectory()){
        File[] subfiles = f.listFiles();
        for (File subf : subfiles) {
          System.out.print(subf.getName()+",");
        }
        System.out.println();
      }
    }
  }
  
  /**
   * 读取一个目录下的子目录文件名,转化为sql语句
   */
  @Test
  public void readfilename2sql() {
    File file = new File("D:/data/");
    File[] files = file.listFiles();
    for (File f : files) {// 判断不村咋的文件名就删除文件
      if(f.isDirectory()){
        File[] subfiles = f.listFiles();
        StringBuilder sb = new StringBuilder();
        sb.append("update serverpoint set context ='");
        for (File subf : subfiles) {
//          System.out.print(subf.getName()+",");
//          <img  src="/upload/doc/20151102/69591446453206976.jpg"/>
          sb.append("<p><img src=\"/upload/doc/20151125/"+f.getName()+"/"+subf.getName()+"\" style=\"width:650px;float:none;\"/></p>");
        }
        sb.append("' where id ="+f.getName()+";");
        System.out.println(sb);
      }
    }
    System.out.println("commit;");
  }
  
  /**
   * 读取一个目录下的子目录文件名
   */
  @Test
  public void readfilebyno() {
    for (int i = 1; i < 167; i++) {
      File file = new File("D:/git/qwe-project/xyyzglj/src/main/webapp/data/"+i);
      File[] files = file.listFiles();
      for (File f : files) {// 判断不村咋的文件名就删除文件
        System.out.print(f.getName()+",");
      }
      System.out.println();
    }
    
  }
}
